package com.github.fancyideas.repository;

import com.github.fancyideas.module.entity.ShoesHisEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface ShoesHisRepository extends JpaRepository<ShoesHisEntity, Long>, JpaSpecificationExecutor<ShoesHisEntity> {

    @Query(nativeQuery = true, value =
            "select tt.group_month, sum(tt.num) " +
                    "from ( " +
                    "select to_char(t.put_in_date, 'YYYY-MM') as group_month, " +
                    " count(t.*)                        as num " +
                    "from t_shoes t " +
                    "where to_char(t.put_in_date, 'YYYY') = ?1 " +
                    "group by group_month " +
                    "union all " +
                    "select to_char(t.put_in_date, 'YYYY-MM') as group_month," +
                    "count(t.*)                        as num " +
                    "from t_shoes_his t " +
                    "where to_char(t.put_in_date, 'YYYY') = ?1 " +
                    "group by group_month " +
                    ") tt " +
                    "group by tt.group_month"
    )
    List<Map<String, Object>> countShoesEntitiesByYear(String queryMonth);

    @Query(nativeQuery = true, value =
            "select tt.* " +
                    "from ( " +
                    "select t.shoes_id as shoesId," +
                    "c.customer_name customerName," +
                    "c.phone," +
                    "t.operations," +
                    "t.put_in_address as putInAddress," +
                    "t.put_in_date as putInDate " +
                    "from t_shoes t " +
                    "left join t_customer c on t.customer_id = c.customer_id " +
                    "where to_char(t.put_in_date, 'YYYY-MM') = ?1 " +
                    "union all " +
                    "select t.shoes_his_id as shoesId," +
                    "c.customer_name customerName," +
                    "c.phone," +
                    "t.operations," +
                    "t.put_in_address as putInAddress," +
                    "t.put_in_date as putInDate " +
                    "from t_shoes_his t " +
                    "left join t_customer c on t.customer_id = c.customer_id " +
                    "where to_char(t.put_in_date, 'YYYY-MM') = ?1 " +
                    ") tt "
    )
    List<Map<String, Object>> queryPutinShoesDetailByMonth(String queryMonth);

    @Query(nativeQuery = true, value =
            "select to_char(take_out_date, 'YYYY-MM') as group_month," +
                    "sum(cost) as totalMoney," +
                    "count(*) as total from t_shoes_his " +
                    "where to_char(take_out_date, 'YYYY') = ?1 " +
                    "group by group_month ")
    List<Map<String, Object>> countShoesTakeOutByMonth(String queryMonth);

    @Query(nativeQuery = true, value =
            "select c.customer_name as customerName, " +
                    "c.phone," +
                    "t.shoes_his_id shoesId," +
                    "t.operations," +
                    "to_char(t.put_in_date,'YYYY-MM-DD hh24:mm:ss') as putInDate," +
                    "t.put_in_address as putInAddress," +
                    "to_char(t.take_out_date,'YYYY-MM-DD hh24:mm:ss') as takeOutDate," +
                    "t.cost " +
                    "from t_shoes_his t " +
                    "left join t_customer c on t.customer_id = c.customer_id " +
                    "where to_char(t.take_out_date, 'YYYY-MM') = ?1 "
    )
    List<Map<String, Object>> queryTakeoutShoesDetailByMonth(String queryMonth);

    @Query(nativeQuery = true, value =
            "select t.customer_id as customerId, " +
            "t.phone," +
            "t.customer_name customerName," +
            "sum(tsh.cost) as cost " +
            "from t_customer t " +
            "left join t_shoes_his tsh on t.customer_id = tsh.customer_id " +
            "where to_char(tsh.take_out_date, 'YYYY-MM') = ?1 "+
            "group by t.customer_name,t.phone,t.customer_id"
    )
    List<Map<String, Object>> syntheticalByMonth(String queryMonth);
}
